with scan_smal_equipment_detail as (
select   
 t.scan_time
,to_date(t.scan_time) as scan_time_dt 
,substring(t.scan_time,12,2) as scan_time_hh
,concat(to_date(t.scan_time),'-',substring(t.scan_time,12,2)) as format_scan_time
,case when substring(t.scan_time,15,2)>='00' and substring(t.scan_time,15,2)<'05' then 1 
      when substring(t.scan_time,15,2)>='05' and substring(t.scan_time,15,2)<'10' then 2 
      when substring(t.scan_time,15,2)>='10' and substring(t.scan_time,15,2)<'15' then 3 
      when substring(t.scan_time,15,2)>='15' and substring(t.scan_time,15,2)<'20' then 4 
      when substring(t.scan_time,15,2)>='20' and substring(t.scan_time,15,2)<'25' then 5 
      when substring(t.scan_time,15,2)>='25' and substring(t.scan_time,15,2)<'30' then 6 
      when substring(t.scan_time,15,2)>='30' and substring(t.scan_time,15,2)<'35' then 7 
      when substring(t.scan_time,15,2)>='35' and substring(t.scan_time,15,2)<'40' then 8 
      when substring(t.scan_time,15,2)>='40' and substring(t.scan_time,15,2)<'45' then 9 
      when substring(t.scan_time,15,2)>='45' and substring(t.scan_time,15,2)<'50' then 10
      when substring(t.scan_time,15,2)>='50' and substring(t.scan_time,15,2)<'55' then 11  
      when substring(t.scan_time,15,2)>='55' and substring(t.scan_time,15,2)<='59'then 12 else 0 end  as scan_time_mm
,t.equipment_code 
,t.supply_desk_code   
,t.network_code as center_code
,t.network_name    as center_name
,t1.agent_code
,t1.agent_name
,t.waybill_no
,t.grid_code
,t.operate_type
,t.package_No
from jms_dwd.dwd_tab_ass_scan_small_upper_base_hi t
join jms_dim.dim_network_whole_massage t1 on t.network_code=t1.code
where t1.network_type=4 
 and t.dt>='{{ execution_date | date_add(-1) | cst_ds }}-00'
 and t.dt<='{{ execution_date | cst_ds }}-23'

),
scan_smal_equipment_sum as ( 
select   
 t.scan_time_dt
,t.scan_time_hh
,t.format_scan_time
,t.equipment_code 
,t.supply_desk_code   
,t.center_code  
,t.center_name    
,t.agent_code
,t.agent_name
,count(waybill_no)  as inductions_num --供件量,按设备维度统计所有操作数据，根据单号+扫描时间去重
,sum(case when grid_code=111 then 1 else 0 end) as valid_sorting_num --有效分拣量,按设备维度统计gridcode=111的操作数据，根据单号+扫描时间去重
,sum(case when grid_code <> 111 then 1 else 0 end) as error_num --异常量,按设备维度统计gridcode≠111的操作数据，根据单号+扫描时间去重
,sum(case when (operate_type=1 and grid_code = 111 and package_No is not null) then 1 else 0 end ) as out_package_num  --出港建包
,sum(case when operate_type=1   then 1 else 0 end ) as out_sorting_num --出港分拣
,sum(case when operate_type=2 and grid_code = 111 and package_No is not null then 1 else 0 end ) as in_package_num --进港建包
,sum(case when operate_type=2   then 1 else 0 end ) as in_sorting_num --进港分拣
,sum(case when grid_code=111 and package_No is not null then 1 else 0 end) as package_num  --建包量   
from scan_smal_equipment_detail t
group by 
 t.scan_time_dt
,t.scan_time_hh
,t.format_scan_time
,t.equipment_code 
,t.supply_desk_code   
,t.center_code  
,t.center_name    
,t.agent_code
,t.agent_name
),
--有效操作时长
scan_smal_equipment_valid_operation_detail as  (
select   
 t.scan_time_dt
,t.scan_time_hh
,t.format_scan_time
,t.equipment_code 
,t.supply_desk_code   
,t.center_code  
,t.center_name    
,t.agent_code
,t.agent_name
,t.scan_time_mm
,count(waybill_no)  as inductions_num --供件量,按设备维度统计所有操作数据，根据单号+扫描时间去重
from scan_smal_equipment_detail t
group by 
 t.scan_time_dt
,t.scan_time_hh
,t.format_scan_time
,t.equipment_code 
,t.supply_desk_code   
,t.center_code  
,t.center_name    
,t.agent_code
,t.agent_name 
,t.scan_time_mm         
),

--有效操作时长
scan_smal_equipment_supply_valid as  (

--供包台

select   
  t.supply_desk_code  
 ,t.equipment_code
 ,t.scan_time_dt
 ,t.scan_time_hh
 ,t.format_scan_time
 ,sum(case when t.scan_time_mm >0 then 1 else 0 end ) *5 as supply_valid
 from scan_smal_equipment_valid_operation_detail t
where t.inductions_num>30
 group by 
  t.supply_desk_code  
 ,t.scan_time_dt
 ,t.scan_time_hh
 ,t.format_scan_time 
 ,t.equipment_code
       
),

scan_smal_equipment_equipment_valid as  (
--交差带
  select 
          t.scan_time_dt
         ,t.scan_time_hh
         ,t.format_scan_time
         ,t.equipment_code
         ,sum(case when t.scan_time_mm >0 then 1 else 0 end ) *5 as equipment_valid
         from(
                  select   
                   t.equipment_code  
                  ,t.scan_time_mm 
                  ,t.scan_time_dt
                  ,t.scan_time_hh,t.format_scan_time
                  ,sum(t.inductions_num) as valid_operation_nums
                  from scan_smal_equipment_valid_operation_detail t
                  group by 
                   t.equipment_code  
                  ,t.scan_time_mm 
                  ,t.scan_time_dt
                  ,t.scan_time_hh,t.format_scan_time 
         ) t where t.valid_operation_nums>30
         group by       
          t.scan_time_dt
         ,t.scan_time_hh
         ,t.format_scan_time
         ,t.equipment_code


),scan_smal_equipment_center_valid as  (

 select 
          t.scan_time_dt
         ,t.scan_time_hh
         ,t.format_scan_time
         ,t.center_code
         ,sum(case when t.scan_time_mm >0 then 1 else 0 end ) *5 as center_valid
         from(
                  select   
                   t.center_code  
                  ,t.scan_time_mm 
                  ,t.scan_time_dt
                  ,t.scan_time_hh,t.format_scan_time
                  ,sum(t.inductions_num) as valid_operation_nums
                  from scan_smal_equipment_valid_operation_detail t
                  group by 
                   t.center_code  
                  ,t.scan_time_mm 
                  ,t.scan_time_dt
                  ,t.scan_time_hh,t.format_scan_time 
         ) t where t.valid_operation_nums>30
         group by       
          t.scan_time_dt
         ,t.scan_time_hh
         ,t.format_scan_time 
         ,t.center_code   
         
),scan_smal_equipment_agent_valid as  (

   select 
          t.scan_time_dt
         ,t.scan_time_hh,t.format_scan_time,t.agent_code

         ,sum(case when t.scan_time_mm >0 then 1 else 0 end ) *5 as agent_valid
         from(
                  select   
                   t.agent_code  
                  ,t.scan_time_mm 
                  ,t.scan_time_dt
                  ,t.scan_time_hh,t.format_scan_time
                  ,sum(t.inductions_num) as valid_operation_nums
                  from scan_smal_equipment_valid_operation_detail t
                  group by 
                   t.agent_code  
                  ,t.scan_time_mm 
                  ,t.scan_time_dt
                  ,t.scan_time_hh,t.format_scan_time 
         ) t where t.valid_operation_nums>30
         group by       
          t.scan_time_dt
         ,t.scan_time_hh,t.format_scan_time ,t.agent_code

),scan_smal_equipment_all_valid as  (
select 
          t.scan_time_dt
         ,t.scan_time_hh
         ,t.format_scan_time
         ,sum(case when t.scan_time_mm >0 then 1 else 0 end ) *5 as all_valid
         from(
                  select    
                   t.scan_time_mm 
                  ,t.scan_time_dt
                  ,t.scan_time_hh,t.format_scan_time
                  ,sum(t.inductions_num) as valid_operation_nums
                  from scan_smal_equipment_valid_operation_detail t
                  group by 
                   t.scan_time_mm 
                  ,t.scan_time_dt
                  ,t.scan_time_hh,t.format_scan_time 
         ) t where t.valid_operation_nums>30
         group by       
          t.scan_time_dt
         ,t.scan_time_hh
         ,t.format_scan_time     
),
--供包台量
scan_smal_equipment_inductions_total as (

select 
t.scan_time_dt
,count(supply_desk_code) as inductions_tota
,equipment_code
from (
         select to_date(t.scan_time) as scan_time_dt
              , supply_desk_code
              , equipment_code
         from jms_dwd.dwd_tab_ass_scan_small_upper_base_hi t
                  join jms_dim.dim_network_whole_massage t1 on t.network_code = t1.code
         where t1.network_type = 4
          and t.dt>='{{ execution_date | date_add(-1) | cst_ds }}-00'
          and t.dt<='{{ execution_date | cst_ds }}-23'
         group by to_date(t.scan_time)
                , supply_desk_code, equipment_code
     ) t
group by t.scan_time_dt, equipment_code

)
insert overwrite table jms_dm.dm_scan_small_equipment_hi partition (dt)

select
 t.scan_time_dt
,t.scan_time_hh
,t.equipment_code 
,t.supply_desk_code   
,t.center_code  
,t.center_name    
,t.agent_code
,t.agent_name
,t.inductions_num

,t.valid_sorting_num--有效分拣量,按设备维度统计gridcode=111的操作数据，根据单号+扫描时间去重
,t.error_num --异常量'按设备维度统计gridcode≠111的操作数据，根据单号+扫描时间去重
,t.out_package_num--出港建包
,t.out_sorting_num--出港分拣
,t.in_package_num--进港建包   
,t.in_sorting_num--进港分拣
,t.package_num --建包量   
,t1.inductions_tota
,nvl(t2.supply_valid,0) supply_valid
,nvl(t3.equipment_valid,0) equipment_valid
,nvl(t4.center_valid,0) center_valid
,nvl(t5.agent_valid,0) agent_valid
,nvl(t6.all_valid,0) all_valid
,t.scan_time_dt as dt
from 
  scan_smal_equipment_sum t 
left join scan_smal_equipment_inductions_total t1 on t.scan_time_dt=t1.scan_time_dt  and t.equipment_code=t1.equipment_code
left join scan_smal_equipment_supply_valid t2 on t.format_scan_time=t2.format_scan_time and t.supply_desk_code=t2.supply_desk_code and t.equipment_code=t2.equipment_code
left join scan_smal_equipment_equipment_valid t3 on t.format_scan_time=t3.format_scan_time and t.equipment_code=t3.equipment_code
left join scan_smal_equipment_center_valid t4 on t.format_scan_time=t4.format_scan_time and t.center_code=t4.center_code
left join scan_smal_equipment_agent_valid t5 on t.format_scan_time=t5.format_scan_time and t.agent_code=t5.agent_code
left join scan_smal_equipment_all_valid t6 on t.format_scan_time=t6.format_scan_time
where     t.scan_time_dt>='{{ execution_date | date_add(-1) | cst_ds }}'
          and t.scan_time_dt<='{{ execution_date | cst_ds }}'
    distribute by 1
;
